﻿using aier.dataaccess.model;
using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
    public class CategoryDal
    {
        public DapperHelper dapperHelper { get; set; }
        public ILog log { get; set; }
        /// <summary>
        /// 获取所有项目
        /// 原程序 ScCategoryServiceImpl/getAll
        /// </summary>
        /// <returns></returns>
        public List<CategoryView> GetAllCategoryInfo()
        {

            List<CategoryView> categoryViews = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_CATEGORY";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                categoryViews = a.Query<CategoryView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return categoryViews;

        }
        /// <summary>
        /// 根据项目ID获取项目
        /// 查询条件：项目ID
        /// 原程序 ScCategoryServiceImpl/getById
        /// </summary>
        /// <returns></returns>
        public CategoryView GetCategoryInfoById(string CategoryId)
        {

            CategoryView categoryView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_CATEGORY  /**where**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (!string.IsNullOrWhiteSpace(CategoryId))
                {
                    sqlBuilder.Where("new_categoryid=@new_categoryid");
                    sqlParams.Add("@new_categoryid", CategoryId);
                }
                categoryView = a.QueryFirstOrDefault<CategoryView>(sqlTemplate.RawSql, sqlParams);
            });

            return categoryView;

        }

        public IEnumerable<CategoryView> GetCategorIdByTmeplateId(Guid templateId)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<CategoryView>>(a =>
            {
                string sql = @"select c.new_categoryid as new_categoryid,c.new_categoryname as new_categoryname,c.new_t_sc_categoryid as new_t_sc_categoryId from new_t_sc_category  c /**leftjoin**/ /**where**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.LeftJoin("new_new_t_sc_page_element_new_t_sc_category ec on ec.new_t_sc_categoryid = c.new_t_sc_categoryId");
                sqlBuilder.Where("ec.new_t_sc_page_elementid = @templateId", new { templateId = templateId });
                log.Info(string.Format("ScreenTemplateDal,GetCategorIdByTmeplateId ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlTemplate.Parameters));
                return a.Query<CategoryView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });

        }
    }
}
